﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;

namespace ExcelSheetOperateion.NPOI.Extend
{
    //傻逼类,还需要老子自己扩展get set cell value
    public static class HSSFSheetExtend
    {
        #region OpenSheet
        public static ISheet OpenSheet(string path, int index = 0)
        {
            ISheet sheet = null;
            //如果Excel被打开,可能会有错误,被占用......
            FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);

            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "SGIDI";
            hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Leica DNA03 Observe Data";
            hssfworkbook.SummaryInformation = si;

            //get the exact sheet to operate
            sheet = hssfworkbook.GetSheetAt(index);
            return sheet;
        }
        #endregion

        #region FindCell



        public static ICell FindCell(this ISheet sheet, string matchstr, bool completeMatch = true, bool byRow = true, int MaxRow = 200, int MaxColumn = 10)
        {
            //按列查找
            if (byRow == false)
            {
                int max = MaxRow;
                MaxRow = MaxColumn;
                MaxColumn = max;
            }
            Func<string, bool> MatchFunc = null;
            if (completeMatch == true)
            {
                MatchFunc = new Func<string, bool>((str) => str == matchstr);
            }
            else
            {
                MatchFunc = new Func<string, bool>((str) => str.Contains(matchstr));
            }
            ICell target = null;
            for (int i = 0; i < MaxRow; i++)
            {
                for (int j = 0; j < MaxColumn; j++)
                {
                    ICell cell = byRow == true ? sheet.GetCell(i, j) : sheet.GetCell(j, i);
                    string str = "";
                    if (cell.CellType == CellType.STRING)
                    {
                        str = cell.StringCellValue;
                        if (MatchFunc(str))
                        {
                            target = cell;
                            return target;
                        }
                    }
       
                }
            }
            return target;
        }
        #endregion




        #region GetCellValue
        public static ICell GetCell(this ISheet sheet, int row, int column)
        {
            IRow r = sheet.GetRow(row);
            if (r == null)
            {
                r = sheet.CreateRow(row);
            }
            ICell cell = r.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
            return cell;
        }

        //extend on sheet
        public static string GetCellValue(this ISheet sheet, int row, int column)
        {
            ICell cell = sheet.GetCell(row, column);

            return cell.StringCellValue;
        }

        public static double GetCellNumber(this ISheet sheet, int row, int column)
        {
            
            ICell cell = sheet.GetCell(row, column);
            return cell.NumericCellValue;
        }
        #endregion
        #region SetCellValue
        public static void SetCell(this ISheet sheet, int row, int column, string value, ICellStyle style = null)
        {
            ICell cell = sheet.GetCell(row, column);
            cell.SetCellValue(value);
            if (style != null)
            {
                cell.CellStyle = style;
            }
        }
        public static void SetCell(this ISheet sheet, int row, int column, double value, ICellStyle style = null)
        {
            ICell cell = sheet.GetCell(row, column);
            cell.SetCellValue(value);
            if (style != null)
            {
                cell.CellStyle = style;
            }
        }
        public static void SetCell(this ISheet sheet, int row, int column, DateTime value, ICellStyle style = null)
        {
            ICell cell = sheet.GetCell(row, column);
            cell.SetCellValue(value);
            if (style != null)
            {
                cell.CellStyle = style;
            }
        }
        #endregion
    }

    public static class HSSFICellExtend
    {
        #region GetCellValue
        //extend on cell
        public static string GetCellValue(this ICell cell)
        {
            if (cell == null)
                return "";
            try
            {
                if (cell.CellType == CellType.STRING || cell.CellType == CellType.FORMULA)
                    return cell.StringCellValue.Trim();
                else
                    return "";
            }
            catch
            {
                return null;
            }
        }
        //extend on cell
        public static double GetCellDouble(this ICell cell)
        {
            if (cell == null)
                return 0;

            if (cell.CellType == CellType.NUMERIC || cell.CellType==CellType.FORMULA)
                return cell.NumericCellValue;
            else
                return 0;
        }
        #endregion

        #region Offset
        public static ICell OffSet(this ICell cell,int rowOff,int columnOff)
        {
            if (cell == null) return null;
            ICell temp = cell.Sheet.GetCell(rowOff  + cell.RowIndex, columnOff + cell.ColumnIndex);
            return temp;
           
        }
        //public static int GetMergedRow(ICell cell)
        //{
        //    ISheet sheet = cell.Sheet;
        //    int row = 0;
        //    ICell temp = cell;
        //    while (temp.RowIndex == cell.RowIndex)
        //    {
        //        row++;
        //        temp = sheet.GetCell(cell.RowIndex + row, cell.ColumnIndex);
        //    }
        //    return row;
        //}
        //public static int GetMergedColumn(ICell cell)
        //{
        //    ISheet sheet = cell.Sheet;
        //    int column = 0;
        //    ICell temp = cell;
         
        //    while (temp.ColumnIndex == cell.ColumnIndex)
        //    {
        //        column++;
        //        temp = sheet.GetCell(cell.RowIndex, cell.ColumnIndex + column);
        //    }
        //    return column;
        //}
        #endregion
    }
}
